﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Text.RegularExpressions;
using ExcelLibrary.SpreadSheet;
using iTextSharp.text.pdf;
using System.IO;
using iTextSharp.text;
using System.Data.OleDb;
using System.Configuration;
using iTextSharp.text.pdf.parser;
using System.Diagnostics;

namespace OverstockPDF
{
    public partial class Form1 : Form
    {
        private int orderNo = 0;
        private int orderDate = 2;
        private int qty = 3;
        private int overstockSku = 4;
        private int vendorSku = 5;
        private int shipDetailsCol = 6;
        private int price = 9;
        private int method = 10;
        private string shipVia = "BEST";
        private HashSet<int> rowIndexPool = new HashSet<int>();
        private string path = "";
        private string tempPath = @"pdf_tmp\";

        public Form1()
        {
            InitializeComponent();
            filePath.Text = ConfigurationManager.AppSettings["Open Orders Excel File Path"];
        }

        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog fileDialog1 = new OpenFileDialog();
            fileDialog1.InitialDirectory = @"C:\"; //Initial
            fileDialog1.Filter = "xls files (*.xls)|*.xls"; //File type
            fileDialog1.FilterIndex = 1;
            fileDialog1.RestoreDirectory = true;
            if (fileDialog1.ShowDialog() == DialogResult.OK)
            {
                filePath.Text = fileDialog1.FileName;
            }
            else
            {
                filePath.Text = "";
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            path = filePath.Text.Substring(0, filePath.Text.LastIndexOf('\\') + 1);
            textBox1.Text = ""; // Clear
            Workbook book = null;
            try
            {
                book = Workbook.Load(filePath.Text);
            }
            catch (DirectoryNotFoundException ex)
            {
                MessageBox.Show("File directory not found!");
                textBox1.Text = ex.Message;
                return;
            }
            catch (FileNotFoundException ex)
            {
                MessageBox.Show("Excel file not found!");
                textBox1.Text = ex.Message;
                return;
            }
            Worksheet sheet = book.Worksheets[0];

            // Traverse rows by Index
            for (int rowIndex = sheet.Cells.FirstRowIndex + 1;
                rowIndex <= sheet.Cells.LastRowIndex; rowIndex++)
            {

                List<Row> rowList = searchResult(sheet, rowIndex);
                if (rowList == null || rowList.Count == 0)
                    continue;

                if (rowList.Count == 1)
                    createWithTemplatePdfSingle(rowList.First());
                else
                    createWithTemplatePdfMultiple(rowList);
            }


            string pdf = mergePdf();
            //Test
            //Row row = sheet.Cells.GetRow(2);

            if (pdf != null)
                Process.Start(pdf);

            // If the code above works, we should see the following
            textBox1.Text += "Result: Success. Please check the logs for detail.";

        }

        private List<Row> searchResult(Worksheet sheet, int rowIndex)
        {
            if (rowIndexPool.Contains(rowIndex))
                return null;

            rowIndexPool.Add(rowIndex);
            List<Row> list = new List<Row>();
            Row rowOriginal = sheet.Cells.GetRow(rowIndex);
            list.Add(rowOriginal);

            if (rowIndex == sheet.Cells.LastRowIndex)
                return list;

            string orderNumber = rowOriginal.GetCell(orderNo).Value.ToString();
            for (int r = rowIndex + 1; r <= sheet.Cells.LastRowIndex; r++)
            {
                Row row = sheet.Cells.GetRow(r);
                if (orderNumber.Equals(row.GetCell(orderNo).Value.ToString()))
                {
                    rowIndexPool.Add(r);
                    list.Add(row);
                }
            }

            return list;
        }

        private void createWithTemplatePdfSingle(Row row)
        {
            // If it exsits in AUI, no more PDF.
            if (ConfigurationManager.AppSettings["AUI Check"].ToString().ToUpper().Equals("YES") &&
                IsExistedInAUI(row.GetCell(orderNo).Value.ToString()))
            {
                textBox1.Text += row.GetCell(orderNo).Value.ToString() + " is existed in AUI.\r\n";
                return;
            }
            // Excel data
            Cell cell = row.GetCell(shipDetailsCol);
            string shipDetails = cell.Value.ToString();
            string[] shipArr = Regex.Split(shipDetails, "        ");

            string file = filePath.Text;

            // Create Temporary Path
            createTmpFolder(path + tempPath);
            // Read the template file
            PdfReader reader = new PdfReader(ConfigurationManager.AppSettings["Pdf Template Path"].ToString() + "Template_1.pdf");
            // Instantiate PDFStamper object
            // The Output file will be created from template file and edited by the PDFStamper
            PdfStamper stamper = new PdfStamper(reader, new FileStream(
                       path + tempPath + row.GetCell(orderNo).Value.ToString()
                       + ".pdf", FileMode.Create));

            // Object to deal with the Output file's textfields
            AcroFields fields = stamper.AcroFields;

            fields.SetField("shipTo", shipArr[0]);
            string sendTo = "";
            for (int i = 0; i < shipArr.Length; i++)
            {
                if (shipArr[i].Trim().Equals(""))
                    continue;

                if (i < 2)
                    sendTo += shipArr[i] + "\r\n";
                else
                    sendTo += shipArr[i] + " ";
            }
            fields.SetField("sendTo", sendTo);
            // Fill the form
            fields.SetField("orderDate", row.GetCell(orderDate).DateTimeValue.ToString("MM/dd/yyyy"));
            fields.SetField("orderNo", row.GetCell(orderNo).Value.ToString());
            fields.SetField("shipVia", shipVia);
            fields.SetField("shipMethod", row.GetCell(method).Value.ToString());
            fields.SetField("qtyOrdered", row.GetCell(qty).Value.ToString());
            fields.SetField("itemNo", row.GetCell(overstockSku).Value.ToString());
            fields.SetField("desc", GetDescFromAccess(row.GetCell(vendorSku).Value.ToString()));
            fields.SetField("qtyShipped", row.GetCell(qty).Value.ToString());
            fields.SetField("vendorSku", row.GetCell(vendorSku).Value.ToString());
            fields.SetField("price", row.GetCell(price).Value.ToString());

            // Form flattening rids the form of editable text fields so
            // The final output can't be edited
            stamper.FormFlattening = true;

            // Closing the stamper
            stamper.Close();

            // Logging on text box
            textBox1.Text += row.GetCell(orderNo).Value.ToString() + ".pdf created successfully!\r\n";

        }

        private void createWithTemplatePdfMultiple(List<Row> rows)
        {
            // Count is bigger than seven.
            if (rows.Count > 7)
            {
                textBox1.Text += rows.First().GetCell(orderNo).Value.ToString() + " has more than 7 records.\r\n";
                return;
            }
            // If it exsits in AUI, no more PDF.
            if (ConfigurationManager.AppSettings["AUI Check"].ToString().ToUpper().Equals("YES") &&
                IsExistedInAUI(rows.First().GetCell(orderNo).Value.ToString()))
            {
                textBox1.Text += rows.First().GetCell(orderNo).Value.ToString() + " is existed in AUI.\r\n";
                return;
            }
            // Excel data
            Cell cell = rows.First().GetCell(shipDetailsCol);
            string shipDetails = cell.Value.ToString();
            string[] shipArr = Regex.Split(shipDetails, "        ");

            string file = filePath.Text;
            path = file.Substring(0, file.LastIndexOf('\\') + 1);
            // Create Temporary Path
            createTmpFolder(path + tempPath);
            // Read the template file
            PdfReader reader = new PdfReader(ConfigurationManager.AppSettings["Pdf Template Path"].ToString() + "Template_" + rows.Count + ".pdf");
            // Instantiate PDFStamper object
            // The Output file will be created from template file and edited by the PDFStamper
            PdfStamper stamper = new PdfStamper(reader, new FileStream(
                       path + tempPath + rows.First().GetCell(orderNo).Value.ToString()
                       + ".pdf", FileMode.Create));

            // Object to deal with the Output file's textfields
            AcroFields fields = stamper.AcroFields;

            fields.SetField("shipTo", shipArr[0]);
            string sendTo = "";
            for (int i = 0; i < shipArr.Length; i++)
            {
                if (shipArr[i].Trim().Equals(""))
                    continue;

                if (i < 2)
                    sendTo += shipArr[i] + "\r\n";
                else
                    sendTo += shipArr[i] + " ";
            }
            fields.SetField("sendTo", sendTo);
            // Fill the form
            fields.SetField("orderDate", rows.First().GetCell(orderDate).DateTimeValue.ToString("MM/dd/yyyy"));
            fields.SetField("orderNo", rows.First().GetCell(orderNo).Value.ToString());
            fields.SetField("shipVia", shipVia);
            fields.SetField("shipMethod", rows.First().GetCell(method).Value.ToString());

            int count = 1;
            foreach (Row row in rows)
            {
                fields.SetField("qtyOrdered_" + count, row.GetCell(qty).Value.ToString());
                fields.SetField("itemNo_" + count, row.GetCell(overstockSku).Value.ToString());
                fields.SetField("desc_" + count, GetDescFromAccess(row.GetCell(vendorSku).Value.ToString()));
                fields.SetField("qtyShipped_" + count, row.GetCell(qty).Value.ToString());
                fields.SetField("vendorSku_" + count, row.GetCell(vendorSku).Value.ToString());
                fields.SetField("price_" + count, row.GetCell(price).Value.ToString());
                count++;
            }

            // Form flattening rids the form of editable text fields so
            // The final output can't be edited
            stamper.FormFlattening = true;

            // Closing the stamper
            stamper.Close();

            // Logging on text box
            textBox1.Text += rows.First().GetCell(orderNo).Value.ToString() + ".pdf created successfully!\r\n";

        }

        private string GetDescFromAccess(string vendorSku)
        {
            string result = "";

            OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
                + ConfigurationManager.AppSettings["Overstock FrontEnd DB Path"].ToString());
            OleDbCommand cmd = new OleDbCommand();
            string sql = "select [Product Name] from [Active Listing Table] where [Partner SKU] = '" + vendorSku + "'";
            cmd.CommandText = sql;
            conn.Open();
            OleDbDataAdapter adpt = new OleDbDataAdapter(sql, conn);
            DataSet ds = new DataSet();
            adpt.Fill(ds);
            result = ds.Tables[0].Rows[0]["Product Name"].ToString();
            conn.Close();

            return result;
        }

        private bool IsExistedInAUI(string orderNo)
        {
            bool rs = false;

            OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
                + ConfigurationManager.AppSettings["Overstock Invoicing FrontEnd DB Path"].ToString());
            OleDbCommand cmd = new OleDbCommand();
            string sql = "select [AUI].* from [AUI] where [Order #] = '" + orderNo + "'";
            cmd.CommandText = sql;
            conn.Open();
            OleDbDataAdapter adpt = new OleDbDataAdapter(sql, conn);
            DataSet ds = new DataSet();
            adpt.Fill(ds);
            if (ds.Tables[0].Rows.Count > 0)
                rs = true;
            conn.Close();

            return rs;
        }

        private string RandomString(int size)
        {
            StringBuilder builder = new StringBuilder();
            Random random = new Random();
            char ch;
            for (int i = 0; i < size; i++)
            {
                ch = Convert.ToChar(Convert.ToInt32(Math.Floor(26 * random.NextDouble() + 65)));
                builder.Append(ch);
            }

            return builder.ToString();
        }

        private void createTmpFolder(string path)
        {
            if (!Directory.Exists(path))
                Directory.CreateDirectory(path);

        }

        private string mergePdf()
        {
            // PDF merge implemented by PdfSharp
            string dest = path + "Overstock_" + RandomString(8) + ".pdf";
            PdfSharp.Pdf.PdfDocument outputDoc = new PdfSharp.Pdf.PdfDocument();

            string[] pdfs = Directory.GetFiles(path + tempPath);
            foreach (string name in pdfs)
            {
                if (name.ToLower().EndsWith(".pdf"))
                {
                    PdfSharp.Pdf.PdfDocument inputDoc = PdfSharp.Pdf.IO.PdfReader.Open(name, PdfSharp.Pdf.IO.PdfDocumentOpenMode.Import);
                    int count = inputDoc.PageCount;
                    for (int i = 0; i < count; i++)
                    {
                        PdfSharp.Pdf.PdfPage page = inputDoc.Pages[i];
                        outputDoc.AddPage(page);
                    }
                }
            }
            try
            {
                outputDoc.Save(dest);
            }
            catch (Exception e)
            {
                textBox1.Text += "Can not save the PDF. Detail: " + e.Message + "\r\n";
                return null;
            }

            return dest;
        }

    }
}
